"""
ComplianceTemplate CRUD 操作
定义所有与数据库交互的函数，隔离业务代码与SQL语句
"""
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import func, select
from typing import Optional, List, Dict, Any, Tuple
from uuid import UUID

from models.complianceTemplate import ComplianceTemplate
from models.templateItem import TemplateItem
from schemas.complianceTemplate import ComplianceTemplateCreate, ComplianceTemplateUpdate, TemplateItemCreate
from log import log_info

async def get_compliance_templates(
    db: AsyncSession, 
    skip: int = 0, 
    limit: int = 100,
    filters: Optional[Dict[str, Any]] = None
) -> Tuple[List[ComplianceTemplate], int]:
    """
    获取合规模板列表，支持分页和过滤

    Args:
        db: 数据库会话
        skip: 跳过记录数
        limit: 返回记录限制
        filters: 过滤条件字典

    Returns:
        包含合规模板列表和总记录数的元组
    """
    query = select(ComplianceTemplate)
    
    # 应用过滤条件
    if filters:
        if filters.get("name"):
            query = query.filter(ComplianceTemplate.name.ilike(f"%{filters['name']}%"))
        if filters.get("assessment_type"):
            query = query.filter(ComplianceTemplate.assessment_type == filters["assessment_type"])
        if filters.get("version"):
            query = query.filter(ComplianceTemplate.version == filters["version"])
        if filters.get("id"):
            query = query.filter(ComplianceTemplate.id == filters["id"])
    # 计算总记录数
    total_result = await db.execute(select(func.count()).select_from(query.subquery()))
    total = total_result.scalar_one()
    
    # 应用分页
    query = query.order_by(ComplianceTemplate.updated_at.desc()).offset(skip).limit(limit)
    result = await db.execute(query)
    templates = result.scalars().all()
    
    log_info(f"Retrieved {len(templates)} compliance templates out of {total}", module="crud.complianceTemplate")
    return templates, total

async def get_compliance_template_by_id(db: AsyncSession, template_id: UUID) -> Optional[ComplianceTemplate]:
    """
    根据ID获取合规模板

    Args:
        db: 数据库会话
        template_id: 合规模板ID

    Returns:
        合规模板对象，如未找到则返回None
    """
    query = select(ComplianceTemplate).where(ComplianceTemplate.id == template_id)
    result = await db.execute(query)
    template = result.scalar_one_or_none()
    
    if template:
        log_info(f"Retrieved compliance template with ID {template_id}", module="crud.complianceTemplate")
    else:
        log_info(f"No compliance template found with ID {template_id}", module="crud.complianceTemplate")
    
    return template

async def get_template_item_by_id(db: AsyncSession, item_id: UUID) -> Optional[TemplateItem]:
    """
    根据ID获取单个模板项

    Args:
        db: 数据库会话
        item_id: 模板项ID

    Returns:
        模板项对象，如未找到则返回None
    """
    query = select(TemplateItem).where(TemplateItem.id == item_id)
    result = await db.execute(query)
    item = result.scalar_one_or_none()
    
    if item:
        log_info(f"Retrieved template item with ID {item_id}", module="crud.complianceTemplate")
    else:
        log_info(f"No template item found with ID {item_id}", module="crud.complianceTemplate")
    
    return item

async def get_template_items(db: AsyncSession, template_id: UUID) -> List[TemplateItem]:
    """
    获取指定模板的所有模板项

    Args:
        db: 数据库会话
        template_id: 模板ID

    Returns:
        模板项列表
    """
    query = select(TemplateItem).where(TemplateItem.template_id == template_id)
    result = await db.execute(query)
    items = result.scalars().all()
    log_info(f"Retrieved {len(items)} template items for template ID {template_id}", module="crud.complianceTemplate")
    return items

async def create_compliance_template(
    db: AsyncSession, 
    template: ComplianceTemplateCreate
) -> ComplianceTemplate:
    """
    创建新的合规模板

    Args:
        db: 数据库会话
        template: 合规模板创建模型

    Returns:
        创建的合规模板对象
    """
    # 从Pydantic模型创建ORM实例（排除items字段）
    template_data = template.model_dump(exclude={"items"})
    db_template = ComplianceTemplate(**template_data)
    
    # 添加到数据库
    db.add(db_template)
    await db.commit()
    await db.refresh(db_template)
    
    # 如果提供了模板项，创建它们
    if template.items:
        for item in template.items:
            db_item = TemplateItem(
                template_id=db_template.id,
                **item.model_dump(exclude={"template_id"})
            )
            db.add(db_item)
        
        await db.commit()
    
    log_info(f"Created new compliance template with ID {db_template.id}", module="crud.complianceTemplate")
    return db_template

async def create_template_item(db: AsyncSession, item: TemplateItemCreate) -> TemplateItem:
    """
    创建新的模板项

    Args:
        db: 数据库会话
        item: 模板项创建模型

    Returns:
        创建的模板项对象
    """
    db_item = TemplateItem(**item.model_dump())
    
    # 添加到数据库
    db.add(db_item)
    await db.commit()
    await db.refresh(db_item)
    
    log_info(f"Created new template item with ID {db_item.id}", module="crud.complianceTemplate")
    return db_item

async def update_compliance_template(
    db: AsyncSession, 
    template_id: UUID, 
    template_update: ComplianceTemplateUpdate
) -> Optional[ComplianceTemplate]:
    """
    更新现有合规模板

    Args:
        db: 数据库会话
        template_id: 要更新的合规模板ID
        template_update: 包含更新数据的模型

    Returns:
        更新后的合规模板对象，如未找到则返回None
    """
    # 获取现有合规模板
    db_template = await get_compliance_template_by_id(db, template_id)
    if db_template is None:
        log_info(f"Update failed: No compliance template found with ID {template_id}", module="crud.complianceTemplate")
        return None
    
    # 更新非空字段
    update_data = template_update.model_dump(exclude_unset=True)
    
    for key, value in update_data.items():
        setattr(db_template, key, value)
    
    await db.commit()
    await db.refresh(db_template)
    
    log_info(f"Updated compliance template with ID {template_id}", module="crud.complianceTemplate")
    return db_template

async def delete_compliance_template(db: AsyncSession, template_id: UUID) -> bool:
    """
    删除合规模板

    Args:
        db: 数据库会话
        template_id: 要删除的合规模板ID

    Returns:
        删除成功返回True，未找到则返回False
    """
    # 获取现有合规模板
    db_template = await get_compliance_template_by_id(db, template_id)
    if db_template is None:
        log_info(f"Delete failed: No compliance template found with ID {template_id}", module="crud.complianceTemplate")
        return False
    
    # 删除模板（由于设置了cascade，相关的模板项会自动删除）
    await db.delete(db_template)
    await db.commit()
    
    log_info(f"Deleted compliance template with ID {template_id}", module="crud.complianceTemplate")
    return True

async def get_template_items_by_filters(
    db: AsyncSession,
    template_id: UUID,
    filters: Optional[Dict[str, Any]] = None
) -> List[TemplateItem]:
    """
    根据模板ID和多种过滤条件获取模板项列表
    
    Args:
        db: 数据库会话
        template_id: 模板ID
        filters: 过滤条件字典，支持以下字段：
            - title: 标题过滤（支持模糊匹配）
            - compliance_type: 合规类型过滤（risk/guarantee）
            - maturity_level: 成熟度等级过滤
    
    Returns:
        模板项列表
    """
    query = select(TemplateItem).where(TemplateItem.template_id == template_id)
    
    # 应用过滤条件
    if filters:
        if filters.get("title"):
            query = query.filter(TemplateItem.title.ilike(f"%{filters['title']}%"))
        if filters.get("compliance_type"):
            query = query.filter(TemplateItem.compliance_type == filters["compliance_type"])
        if filters.get("maturity_level"):
            query = query.filter(TemplateItem.maturity_level == filters["maturity_level"])
    
    result = await db.execute(query)
    items = result.scalars().all()
    
    # 构建过滤条件描述用于日志
    filter_desc = []
    if filters:
        if filters.get("title"):
            filter_desc.append(f"标题: '{filters['title']}'")
        if filters.get("compliance_type"):
            filter_desc.append(f"合规类型: '{filters['compliance_type']}'")
        if filters.get("maturity_level"):
            filter_desc.append(f"成熟度等级: {filters['maturity_level']}")
    
    filter_str = "，".join(filter_desc) if filter_desc else "无过滤条件"
    
    log_info(f"查询到 {len(items)} 个模板项，模板ID: {template_id}，过滤条件: {filter_str}", 
             module="crud.complianceTemplate")
    return items

async def delete_template_item(db: AsyncSession, item_id: UUID) -> bool:
    """
    删除模板项

    Args:
        db: 数据库会话
        item_id: 要删除的模板项ID

    Returns:
        删除成功返回True，未找到则返回False
    """
    # 获取现有模板项
    query = select(TemplateItem).where(TemplateItem.id == item_id)
    result = await db.execute(query)
    db_item = result.scalar_one_or_none()
    if db_item is None:
        log_info(f"Delete failed: No template item found with ID {item_id}", module="crud.complianceTemplate")
        return False
    
    # 删除模板项
    await db.delete(db_item)
    await db.commit()
    
    log_info(f"Deleted template item with ID {item_id}", module="crud.complianceTemplate")
    return True
